<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2021-04-28
 * Time: 21:42
 */
namespace app\admin\model\traits;
use think\facade\Db;

class TablHandle
{
    /**处理表字段
     * @param array $data
     * @param string $table
     * @return array
     */
    public static function CommentToTabl(array $data,string $table=''){
//        echo '<pre>';
//        print_r($data);
//        exit;
        foreach ($data as $key=>$value){
            if(isset($value['Comment'])){
                $newdata = self::CommentConversionField($value['Comment']);
            }elseif (isset($value['comment'])){
                $newdata = self::CommentConversionField($value['comment']);
            }
            if(isset($value['Type'])){
                $newdata = array_merge($newdata,self::TypeToField($value['Type']));
            }elseif (isset($value['type'])){
                $newdata = array_merge($newdata,self::TypeToField($value['type']));
            }
            if(isset($value['Field'])){
                $newdata['field'] = $value['Field'];
            }elseif (isset($value['field'])){
                $newdata['field'] = $value['field'];
            }
            if(isset($value['Null'])){
                $newdata['is_null'] = $value['Null']=='YES'?1:0;
            }else{
                $newdata['is_null'] = $value['null']==0;
            }
//            $newdata['field'] = $value['Field'];
            $newdata['table'] = $table;
            $data[$key] = $newdata;
        }
        return $data;
    }

    /**更新单表
     * @param $dan_table
     * @throws \think\db\exception\DbException
     */
    public static function updatetable($dan_table,$qianzui='',$mysql=''){
        if(empty($qianzui)){
            $qianzui = getDataBaseConfig('prefix');
        }
        if(empty($mysql)){
            $mysql = getDataBaseConfig('database');
        }
        $sql = "select TABLE_NAME  AS 'table',TABLE_COMMENT as 'name',engine as 'engine' from information_schema.tables where table_schema='".$mysql."'";
        $array = Db::query($sql);
        foreach ($array as $k=>$value){
            $table = $value['table'];
            $tables = str_replace($qianzui,'',$table);
//            dump($dan_table);die();
            if($dan_table==$tables){
                $zhujian = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA. COLUMNS WHERE TABLE_NAME= '".$qianzui.$tables."' AND COLUMN_KEY = 'PRI';";
                $zhujian = Db::query($zhujian);
                $zhujian = $zhujian[0]['COLUMN_NAME']??'';
                $models = Db::name('system_model')
                    ->where('table',$tables)
                    ->findOrEmpty();
                if(empty($models)){
//                    dump($value);die();
                    $newsql = 'show full fields from '.$table;
                    $newarray = Db::query($newsql);
                    $newarray = TablHandle::CommentToTabl($newarray,$tables);
                    foreach ($newarray as $key=>$va){
                        if($va['field']=='create_time'||$va['field']=='update_time'||$va['field']=='status'){
                            $va['sort'] = 10;
                            if($va['field']=='status'||$va['field']=='create_time'){
                                $va['show'] = 1;
                                if($va['field']=='status'){
                                    $va['edit'] = 1;
                                }
                            }
                        }
                        if($va['field']=='delete_time'){
                            $va['sort'] = 0;
                        }
                        if($va['field']=='id'){
                            $va['sort'] = 9999;
                            $va['show'] = 1;
                            $va['is_key'] = 1;
                        }
                        if($va['field']=='name'){
                            $va['sort'] = 9000;
                        }
                        if($va['field']==$zhujian){
                            $va['is_key'] = 1;
                        }
                        if($va['field']=='status'){
                            $va['default'] = 1;
                        }
                        $field = Db::name('system_field')
                            ->where('table',$tables)
                            ->where('field',$va['field'])
                            ->findOrEmpty();
//                        dump($field);die();
                        if(empty($field)){
                            $va['create_time'] = time();
                            $va['update_time'] = time();
                            Db::name('system_field')
                                ->insert($va);
                        }else{
                            $va['update_time'] = time();
                            Db::name('system_field')
                                ->where('id',$field['id'])
                                ->update($va);
                        }
                        $newarray[$key] = $va;
                    }
                    $model = [
                        'name'=>$value['name'],
                        'table'=>$dan_table,
                        'engine'=>$value['engine'],
                        'tabletype'=>'ordinary',
                        'is_page'=>1,
                        'create_time'=>time(),
                        'update_time'=>time(),
                        'status'=>1,
                    ];
                    $bo = Db::name('system_model')
                        ->insert($model);
                    $value['model'] = $newarray;
                }else{
                    $newarray = [];
                    $newarray['engine'] = $value['engine'];
                    $newarray['name'] = $value['name'];
                    $newarray['update_time'] = time();
                    Db::name('system_model')
                        ->where('id',$models['id'])
                        ->update($newarray);
                    $newsql = 'show full fields from '.$table;
                    $newarray = Db::query($newsql);
//                echo '<pre>';
//                print_r($newarray);
//                exit;
                    $newarray = TablHandle::CommentToTabl($newarray,$tables);
                    foreach ($newarray as $key=>$va){
                        $field = Db::name('system_field')
                            ->where('table',$tables)
                            ->where('field',$va['field'])
                            ->findOrEmpty();
                        if(empty($field)){
                            if($va['field']==$zhujian){
                                $va['is_key'] = 1;
                            }
                            $va['create_time'] = time();
                            $va['update_time'] = time();
                            Db::name('system_field')
                                ->insert($va);
                        }else{
                            if($va['field']==$zhujian){
                                $va['is_key'] = 1;
                            }
                            $va['update_time'] = time();
//                            echo '<pre>';
//                            print_r($field);
//                            print_r($va);
//                            exit;
                            Db::name('system_field')
                                ->where('id',$field['id'])
                                ->update($va);
                        }
                        $newarray[$key] = $va;
                    }
                    $value['model'] = $newarray;
                }
            }
        }
        return true;
    }
    protected static function updatetableField(){

    }

    /**强制更新
     * @param $dan_table
     * @param string $qianzui
     * @param string $mysql
     * @return bool
     * @throws \think\db\exception\DbException
     */
    public static function updatetables($dan_table,$qianzui='',$mysql=''){
        if(empty($qianzui)){
            $qianzui = getDataBaseConfig('prefix');
        }
        if(empty($mysql)){
            $mysql = getDataBaseConfig('database');
        }
        $sql = "select TABLE_NAME  AS 'table',TABLE_COMMENT as 'name',engine as 'engine' from information_schema.tables where table_schema='".$mysql."'";
        $array = Db::query($sql);
        foreach ($array as $k=>$value){
            $table = $value['table'];
            $tables = str_replace($qianzui,'',$table);
            if($dan_table==$tables){
                $models = Db::name('system_model')
                    ->where('table',$tables)
                    ->findOrEmpty();
                if(empty($models)){
                    $data = [
                        'name'=>$value['name'],
                        'table'=>$tables,
                        'engine'=>$value['engine'],
                        'prefix'=>'',
                        'create_time'=>time(),
                        'update_time'=>time(),
                    ];
                    $data['id'] = Db::name('system_model')
                        ->insertGetId($data);
                    $newsql = 'show full fields from '.$table;
                    $newarray = Db::query($newsql);
                    $newarray = TablHandle::CommentToTabl($newarray,$tables);
                    foreach ($newarray as $key=>$va){
                        if($va['field']=='create_time'||$va['field']=='update_time'||$va['field']=='status'){
                            $va['sort'] = 10;
                            if($va['field']=='status'||$va['field']=='create_time'){
                                $va['show'] = 1;
                                if($va['field']=='status'){
                                    $va['edit'] = 1;
                                }
                            }
                        }
                        if($va['field']=='delete_time'){
                            $va['sort'] = 0;
                        }
                        if($va['field']=='id'){
                            $va['sort'] = 9999;
                            $va['show'] = 1;
                        }
                        if($va['field']=='name'){
                            $va['sort'] = 9000;
                        }
                        $field = Db::name('system_field')
                            ->where('table',$tables)
                            ->where('field',$va['field'])
                            ->findOrEmpty();
                        if(empty($field)){
                            $va['create_time'] = time();
                            $va['update_time'] = time();
                            Db::name('system_field')
                                ->insert($va);
                        }else{
                            $va['update_time'] = time();
                            Db::name('system_field')
                                ->where('id',$field['id'])
                                ->update($va);
                        }
                        $newarray[$key] = $va;
                    }
                    $value['model'] = $newarray;
                }else{
                    $newarray = [];
                    $newarray['engine'] = $value['engine'];
                    $newarray['name'] = $value['name'];
                    $newarray['update_time'] = time();
                    Db::name('system_model')
                        ->where('id',$models['id'])
                        ->update($newarray);
                    $newsql = 'show full fields from '.$table;
                    $newarray = Db::query($newsql);
//                echo '<pre>';
//                print_r($newarray);
//                exit;
                    $newarray = TablHandle::CommentToTabl($newarray,$tables);
//                    if($dan_table=='house_rent'){
//                        echo '<pre>';
//                        print_r($newarray);
//                        exit;
//                    }
                    foreach ($newarray as $key=>$va){
                        $field = Db::name('system_field')
                            ->where('table',$tables)
                            ->where('field',$va['field'])
                            ->findOrEmpty();
                        if(empty($field)){
                            $va['create_time'] = time();
                            $va['update_time'] = time();
                            Db::name('system_field')
                                ->insert($va);
                        }else{
                            $va['update_time'] = time();
                            Db::name('system_field')
                                ->where('id',$field['id'])
                                ->update($va);
                        }
                        $newarray[$key] = $va;
                    }
                    $value['model'] = $newarray;
                }
            }
//            $array[$k] = $value;
        }
        return true;
    }

    /**将type转化成字段
     * @param string $type
     * @return mixed
     */
    protected static function TypeToField(string $type=''){
        $type_start = mb_stripos($type,'(');
        $type_end = mb_stripos($type,')');
//        $newtype = '';
        $lang = '';
        if(($type_start!==false)&&($type_end!==false)){
            $lang = mb_substr($type,$type_start+1,($type_end-$type_start-1));
            $newtype = mb_substr($type,0,$type_start);
        }else{
            $newtype = $type;
        }
        $newdata['lang'] = $lang;
        $newdata['type'] = $newtype;
        return $newdata;
    }

    /**comment转化成表字段
     * @param string $comment
     * @param string $filed
     * @return mixed
     */
    protected static function CommentConversionField(string $comment='',$filed = ''){
        if(empty($comment)){
            $newdata['formtype'] = 'input';
            $newdata['option'] = '';
            if($filed=='id'){
                $newdata['show'] = 1;
            }else{
                $newdata['show'] = 0;
            }
            $newdata['edit'] = 0;
            $newdata['search'] = 0;
            $newdata['total'] = 0;
            $newdata['export'] = 0;
            if($filed=='id'){
                $newdata['name'] = 'ID';
            }else{
                $newdata['name'] = '';
            }
            return $newdata;
        }
        $newcomment_min = 0;
        $newdata = [];
        //处理表单类型
        $type_start = mb_stripos($comment,'{');
        $type_end = mb_stripos($comment,'}');
        $newdata['formtype'] = 'input';
        if(($type_start!==false)&&($type_end!==false)){
            if($newcomment_min==0){
                $newcomment_min = $type_start;
            }
            $str = mb_substr($comment,$type_start+1,($type_end-$type_start-1));
            $newdata['formtype'] = $str;
        }

        //处理选项
        $option_start = mb_stripos($comment,'(');
        $option_end = mb_stripos($comment,')');
        if(($option_start!==false)&&($option_end!==false)){
            if($newcomment_min==0){
                $newcomment_min = $option_start;
            }else{
                $newcomment_min = $newcomment_min<$option_start?$newcomment_min:$option_start;
            }
            $option = mb_substr($comment,$option_start+1,($option_end-$option_start-1));
            $array = explode(',',$option);
            $option = '';
            foreach ($array as $value){
                if($value){
                    $option_array = explode(':',$value);
                    if(count($option_array)==1){
                        if(!empty($option_array[0])){
                            $option .= $option_array[0].':'.$option_array[0].',';
                        }

                    }elseif(count($option_array)==2){
                        $option .= $option_array[0].':'.$option_array[1].',';
                    }
                }
            }
            $newdata['option'] = $option;
        }
        //处理
        $s_start = mb_stripos($comment,'[');
        $s_end = mb_stripos($comment,']');
        if(($s_start!==false)&&($s_end!==false)){
            if($newcomment_min==0){
                $newcomment_min = $s_start;
            }else{
                $newcomment_min = $newcomment_min<$s_start?$newcomment_min:$s_start;
            }
            $newstr = mb_substr($comment,$s_start+1,($s_end-$s_start-1));
            $newstr_array = explode(',',$newstr);
            foreach ($newstr_array as $value){
                if(!empty($value)){
                    $value_array = explode(':',$value);
                    $newdata[$value_array[0]] = $value_array[1];
                }
            }
        }
        if(!isset($newdata['show'])){
            $newdata['show'] = 0;
        }
        if(!isset($newdata['edit'])){
            $newdata['edit'] = 0;
        }
        if(!isset($newdata['search'])){
            $newdata['search'] = 0;
        }
        if(!isset($newdata['total'])){
            $newdata['total'] = 0;
        }
        if(!isset($newdata['export'])){
            $newdata['export'] = 0;
        }
//        echo $newcomment_min;exit;
        if($newcomment_min==0){
            $newdata['name'] = $comment;
        }else{
            $newdata['name'] = mb_substr($comment,0,$newcomment_min);
        }
        return $newdata;
    }

    /**获取当前名
     * @param $name
     * @return array
     */
    public static function getClassName($name){
        $array = explode('\\',$name);
        return $array;
    }

    /**将option的str转化成数组
     * @param string $str
     * @return array
     */
    public static function strToArray(string $str = ''){
        $newdata = [];
        $array = explode(',',$str);
        $num = 0;
        foreach ($array as $value){
            if($value){
                $newarray = explode(':',$value);
                if(count($newarray)==1){
                    if($newarray[0]){
                        $newdata[$newarray[0]] = [
                            'name'=>$newarray[0],
                            'num'=>$num
                        ];
                        $num++;
                    }
                }elseif (count($newarray)==2){
                    $newdata[$newarray[0]] = [
                        'name'=>$newarray[1],
                        'num'=>$num
                    ];
                    $num++;
                }
            }
        }
        return $newdata;
    }

    /**option转array
     * @param string $str
     * @return array
     */
    public static function optionToArray(string $str = ''){
        $newdata = [];
        $array = explode(',',$str);
        $num = 0;
        foreach ($array as $value){
            if($value){
                $newarray = explode(':',$value);
                if(count($newarray)==1){
                    if($newarray[0]){
                        $newdata[$newarray[0]] = $newarray[0];
                        $num++;
                    }
                }elseif (count($newarray)==2){
                    $newdata[$newarray[0]] = $newarray[1];
                    $num++;
                }
            }
        }
        return $newdata;
    }

    /**将table字段转化成字符串
     * @param string $table_name
     * @param string $table_id
     * @return string
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public static function TableToFile($table_name='',$table_id=''){
        if(empty($table_name)&&empty($table_id)){
            return '';
        }
        if(isset($table_name)&&$table_name){
            $data = Db::name('system_model')
                ->where('table',$table_name)
                ->field('name,table,engine,tabletype,prefix,is_page')
                ->find();
        }
        if(isset($table_id)&&$table_id){
            $data = Db::name('system_model')
                ->where('id',$table_id)
                ->field('name,table,engine,tabletype,prefix,is_page')
                ->find();
        }
        if(empty($data)){
            return '';
        }
        $field = Db::name('system_field')
            ->where('table',$data['table'])
            ->field('name,field,type,lang,is_null,formtype,table,show,edit,search,total,export,sort,option,join_table')
            ->select()->toArray();
        $newfield = [];
        foreach ($field as $key=>$value){
            if(isset($value['option'])&&$value['option']){
                $option = self::optionToArray($value['option']);
            }else{
                $option = [];
            }
            $value['option'] = $option;
            $newfield[$value['field']] = $value;
        }
        $data['field'] = $newfield;
        return json_encode($data,JSON_UNESCAPED_UNICODE);
    }

    /**获取转化字段类型
     * @param array $field
     * @return array
     */
    public static function conversionField($field=[]){
        foreach ($field as $key=>$value){
            if(in_array($value['formtype'],['lselect','lradio','lcheckbox'])){
                if(isset($value['table'])&&$value['table']){
                    $shuju = Db::name($value['join_table'])
                        ->where('status',1)
                        ->select()->toArray();
                    $value['option'] = $shuju;
                    if(!(isset($value['foreign_key'])&&$value['foreign_key'])){
                        $value['foreign_key'] = 'name';
                    }
                }else{
                    $value['option'] = [];
                }
            }
            $field[$key] = $value;
        }
        return $field;
    }

    /**更新字段
     * @param array $data
     * @return bool
     */
    public static function UpdateField(array  $originaldata=[],array $newdata=[]){
        $table = getDataBaseConfig('prefix').$originaldata['table'];
        $field = $originaldata['field'];
        $newfield = $newdata['field'];

        if($field==$newfield){
            $sql = "alter table `$table` modify column `$field` ";
        }else{
            $sql = "alter  table `$table` change `$field` `$newfield` ";
        }

//        $sql = "alter table `$table` modify column `$field` ";
        $type = '';
        if(isset($newdata['type'])&&$newdata['type']){
            if(isset($newdata['lang'])&&$newdata['lang']){
                $type = $newdata['type']."(".$newdata['lang'].") ";
            }else{
                $type = $newdata['type']." ";
            }
        }
        $sql .= $type;
        $default = '';

        if(strpos($newdata['type'],'text')!==false){
//            $da = $data['default'];
//            $default .= "DEFAULT NULL ";
//            return 1;
        }elseif (in_array($newdata['type'],['varchar'])){
//            return 2;
            $default .= "DEFAULT ";
            $default .= ($newdata['default']??null)?("'".$newdata['default']."' "):"'' ";
        }elseif ((strpos($newdata['type'],'int')!==false)||in_array($newdata['type'],['float'])){
            if(isset($newdata['default'])&&$newdata['default']){
//                return 3;
                $default .= "DEFAULT ".$newdata['default']." ";
            }else{
//                return 4;
                $default .= "DEFAULT 0 ";
            }
        }else{
            $da = $newdata['default'];
            $default .= "DEFAULT '$da' ";
        }
//        return $newdata['formtype'];
//        return $default;
        $sql .= $default;
        $comment = ''.$newdata["name"];
        if(isset($newdata['formtype'])&&$newdata['formtype']){
            $comment .= "{".$newdata['formtype']."}";
        }
        if(isset($newdata['option'])&&$newdata['option']){
            $comment .= "(".$newdata['option'].")";
        }
        $comment .= '[';
        if(isset($newdata['show'])&&$newdata['show']){
            $comment .= "show:".($newdata['show']??0).',';
        }
        if(isset($newdata['edit'])&&$newdata['edit']){
            $comment .= "edit:".($data['edit']??0).',';
        }
        if(isset($newdata['search'])&&$newdata['search']){
            $comment .= "search:".($newdata['search']??0).',';
        }
        if(isset($newdata['total'])&&$newdata['total']){
            $comment .= "total:".($newdata['total']??0).',';
        }
        if(isset($newdata['export'])&&$newdata['export']){
            $comment .= "export:".($newdata['export']??0).',';
        }
        if(isset($newdata['sort'])&&$newdata['sort']){
            $comment .= "sort:".($newdata['sort']??'').',';
        }
        if(isset($newdata['join_table'])&&$newdata['join_table']){
            $comment .= "join_table:".($newdata['join_table']??'').',';
        }
        if(isset($newdata['default'])&&$newdata['default']){
            $comment .= "default:".($newdata['default']??'').',';
        }
        if(isset($newdata['foreign_key'])&&$newdata['foreign_key']){
            $comment .= "foreign_key:".($newdata['foreign_key']??'').',';
        }
        if(isset($newdata['status'])&&$newdata['status']){
            $comment .= "status:".($newdata['status']??1).',';
        }
        if(isset($newdata['describe'])&&$newdata['describe']){
            $comment .= "describe:".($newdata['describe']??'').',';
        }
        $comment = trim($comment,',');
        $comment .= ']';
//        return $comment;
//        echo $comment;exit;
        $sql .= "COMMENT '$comment'";
        try{
            Db::query($sql);
        }catch (\Exception $e){
            return false;
        }
        return true;
    }

    /**添加字段
     * @param array $data
     * @return bool
     */
    public static function AddField(array $data=[]){
        $table = $data['table'];
        $table = getDataBaseConfig('prefix').$table;
        $field = $data['field'];
        $sql = "ALTER TABLE `$table` ADD COLUMN `$field` ";
        $type = '';
        if(isset($data['type'])&&$data['type']){
            if(isset($data['lang'])&&$data['lang']){
                $type = $data['type']."(".$data['lang'].") ";
            }else{
                $type = $data['type']." ";
            }
        }
        $sql .= $type;
        $default = '';

        if(strpos($data['type'],'text')!==false){
//            $da = $data['default'];
//            $default .= "DEFAULT NULL ";
//            return 1;
        }elseif (in_array($data['type'],['varchar'])){
//            return 2;
            $default .= "DEFAULT ";
            $default .= ($data['default']??null)?("'".$data['default']."' "):"'' ";
        }elseif ((strpos($data['type'],'int')!==false)||in_array($data['type'],['float'])){
            if(isset($data['default'])&&$data['default']){
//                return 3;
                $default .= "DEFAULT ".$data['default']." ";
            }else{
//                return 4;
                $default .= "DEFAULT 0 ";
            }
        }else{
            $da = $data['default'];
            $default .= "DEFAULT '$da' ";
        }
//        return $default;
        $sql .= $default;
        $comment = ''.$data["name"];
        if(isset($data['formtype'])&&$data['formtype']){
            $comment .= "{".$data['formtype']."}";
        }
        if(isset($data['option'])&&$data['option']){
            $comment .= "(".$data['option'].")";
        }
        $comment .= '[';
        if(isset($data['show'])&&$data['show']){
            $comment .= "show:".($data['show']??0).',';
        }
        if(isset($data['edit'])&&$data['edit']){
            $comment .= "edit:".($data['edit']??0).',';
        }
        if(isset($data['search'])&&$data['search']){
            $comment .= "search:".($data['search']??0).',';
        }
        if(isset($data['total'])&&$data['total']){
            $comment .= "total:".($data['total']??0).',';
        }
        if(isset($data['export'])&&$data['export']){
            $comment .= "export:".($data['export']??0).',';
        }
        if(isset($data['sort'])&&$data['sort']){
            $comment .= "sort:".($data['sort']??'').',';
        }
        if(isset($data['join_table'])&&$data['join_table']){
            $comment .= "join_table:".($data['join_table']??'').',';
        }
        if(isset($data['default'])&&$data['default']){
            $comment .= "default:".($data['default']??'').',';
        }
        if(isset($data['foreign_key'])&&$data['foreign_key']){
            $comment .= "foreign_key:".($data['foreign_key']??'').',';
        }
        if(isset($data['status'])&&$data['status']){
            $comment .= "status:".($data['status']??1).',';
        }
        if(isset($data['describe'])&&$data['describe']){
            $comment .= "describe:".($data['describe']??'').',';
        }
        $comment = trim($comment,',');
        $comment .= ']';
        $sql .= "COMMENT '$comment'";
//        return $sql;
        try{
            Db::query($sql);
        }catch (\Exception $e){
            return false;
        }
        return true;
    }

    /**删除字段
     * @param array $data
     * @return bool
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public static function DeleteField($data){
//        return $data;
//        $array = Db::name('system_field')
//            ->where('id','in',$data)
//            ->select()->toArray();
//        return $array;
        foreach ($data as $value){
            $table = getDataBaseConfig('prefix').$value['table'];
            $field = $value['field'];
            $sql = "ALTER TABLE `$table` DROP COLUMN `$field`";
//            return $sql;
            try{
                Db::query($sql);
            }catch (\Exception $e){
                return false;
            }
        }
        return true;
    }
}